
package cn.hz.wolf;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.Connection;
public class SelectSql {
     public static String sqlTnx="SELECT t.*,TO_SECONDS(NOW())-TO_SECONDS(t.trx_started) idle_time FROM INFORMATION_SCHEMA.INNODB_TRX t WHERE t.trx_state='LOCK WAIT' ";
    
     public static String sqlSleep= " select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join        "+
    		 "    information_schema.PROCESSLIST b                                                                                                                                     "+
    		 "   on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'                                                                                                                 "+
    		 "   inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID                                                                                                    "+
    		 "    inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;                                                                              ";
    		      
    
     public static String sqlWaits=    
    		  "    SELECT                                                                                      "+
    				  "    ps.id 'PROCESS ID',                                                                         "+
    				  "    ps.USER,                                                                                    "+
    				  "    ps.HOST,                                                                                    "+
    				  "    esh.EVENT_ID,                                                                               "+
    				  "    trx.trx_started,                                                                            "+
    				  "    esh.event_name 'EVENT NAME',                                                                "+
    				  "    esh.sql_text 'SQL',                                                                         "+
    				  "    ps.time                                                                                     "+
    				  "  FROM                                                                                          "+
    				  "    PERFORMANCE_SCHEMA.events_statements_history esh                                            "+
    				  "    JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id                          "+
    				  "    JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id                         "+
    				  "    LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id              "+
    				  "  WHERE                                                                                         "+
    				  "    trx.trx_id IS NOT NULL                                                                      "+
    				  "    AND ps.USER != 'SYSTEM_USER'    AND th.processList_command='Sleep'    "
    				  + "AND (event_name LIKE  '%update%' OR event_name LIKE '%delete%') AND ps.time>100                   "+
    				  "  ORDER BY                                                                                      "+
    				  "    esh.EVENT_ID;                                                                               ";
     
     public static String sqlblooking=    
    		   "    SELECT                                                                            "+
    				   "   tmp.*,                                                                             "+
    				   "   c.SQL_Text blocking_sql_text,                                                      "+
    				   "   p.HOST blocking_host                                                               "+
    				   " FROM                                                                                 "+
    				   "   (                                                                                  "+
    				   "   SELECT                                                                             "+
    				   "     r.trx_state wating_trx_state,                                                    "+
    				   "     r.trx_id waiting_trx_id,                                                         "+
    				   "     r.trx_mysql_thread_Id waiting_thread,                                            "+
    				   "     r.trx_query waiting_query,                                                       "+
    				   "     b.trx_state blocking_trx_state,                                                  "+
    				   "     b.trx_id blocking_trx_id,                                                        "+
    				   "     b.trx_mysql_thread_id blocking_thread,                                           "+
    				   "     b.trx_query blocking_query                                                       "+
    				   "   FROM                                                                               "+
    				   "     information_schema.innodb_lock_waits w                                           "+
    				   "     INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id       "+
    				   "     INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id     "+
    				   "   ) tmp,                                                                             "+
    				   "   information_schema.PROCESSLIST p,                                                  "+
    				   "   PERFORMANCE_SCHEMA.events_statements_current c,                                    "+
    				   "   PERFORMANCE_SCHEMA.threads t                                                       "+
    				   " WHERE                                                                                "+
    				   "   tmp.blocking_thread = p.id                                                         "+
    				   "   AND t.thread_id = c.THREAD_ID                                                      "+
    				   "   AND t.PROCESSLIST_ID = p.id                                                        ";
     
     
     public static String sqlWaiting= " SELECT * FROM sys.innodb_lock_waits";
}